﻿CREATE VIEW spm.SsasPartitionPeriods
AS
/*
 * Copyright (c) 2015 Dr. John Tunnicliffe, Decision Analytics
 * You are free to use this software under the terms of the licence: https://SsasPartitionManager.codeplex.com/license
 *
 * SsasPartitionPeriods view to support the SSAS Partition Manager.  
 * See https://SsasPartitionManager.codeplex.com/documentation for full documentation.
 *
 * This is the view you need to tailor to match your cube definition and specific partitioning requirements. 
 * It contains hard-coded MDX. In this case, the hard-coded MDX is specific to the date dimension of the Adventure Works cube.
 *
 * The following are examples of the MDX unique names required by the Date dimension in the Adventure Works cube
 * 
 * Key attribute:
 *		[Date].[Date].&[20050116]
 *
 * Levels in the [Date].[Calendar] hierarchy
 *		[Date].[Calendar].[Calendar Year].&[2005]
 *		[Date].[Calendar].[Calendar Semester].&[2008]&[1]
 *		[Date].[Calendar].[Calendar Quarter].&[2008]&[2]
 *		[Date].[Calendar].[Month].&[2005]&[10]
 *		[Date].[Calendar].[Date].&[20050108]
 *
 * Weekly calendar
 *		[Date].[Calendar Weeks].[Calendar Week].&[1]&[2007]
 *
 * Part of the SsasPartitionManager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
 WITH PartitionSchemes AS (
	-- Yearly partition periods
	SELECT  
		N'Y' as PartitionPeriod,
		CalendarYear as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Calendar Year]' as PartitionSliceMember, 
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube e.g. &[2005]
		-- e.g. [Date].[Calendar].[Calendar Year].&[2005]
		N'&[' + CalendarYear + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Quarterly partition periods
	SELECT  
		N'Q' as PartitionPeriod,
		CalendarYear + N'Q' + CalendarQuarter as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Calendar Quarter]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube e.g. &[2008]&[2]
		-- e.g. [Date].[Calendar].[Calendar Quarter].&[2008]&[2]
		N'&[' + CalendarYear + N']&[' + CalendarQuarter + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, CalendarQuarter) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, CalendarQuarter) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Monthly partition periods
	SELECT  
		N'M' as PartitionPeriod,
		CalendarYear + N'M' + CalendarMonth as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Month]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube
		-- e.g. [Date].[Calendar].[Month].&[2005]&[10]
		N'&[' + CalendarYear + N']&[' + CalendarMonth + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, CalendarMonth) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, CalendarMonth) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Weekly partition periods
	SELECT 
		N'W' as PartitionPeriod,
		CalendarYear + N'W' + WeekNumberOfYear as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar Weeks].[Calendar Week]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube
		-- e.g. [Date].[Calendar Weeks].[Calendar Week].&[1]&[2007]
		N'&[' + WeekNumberOfYear + N']&[' + CalendarYear + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, WeekNumberOfYear) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, WeekNumberOfYear) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 
	
	-- Daily partition periods
	SELECT 
		N'D' as PartitionPeriod,
		CalendarYear + N'D' + DayNumberOfYear as PartitionSuffix,
		DailyPartitionSliceMember as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		N'&[' + cast(D.DateKey as nvarchar(8)) + N']' as PartitionSliceKey,
		D.ActualDate as PeriodStartDate,
		D.ActualDate as PeriodEndDate
	FROM spm.SsasDateDimWrapper D
)
SELECT
	PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
UNION
-- Add a special hybrid partition scheme which is Monthly partition periods for 2014 and quarterly partitions for previous years
-- Note that AdventureWorks's date dimension stops in 2014, so we hard code this year for demo purposes
-- Clearly in a prod environment, this should be SARG and using a proper date dimension which has 2015 in it!
SELECT  
	N'H' as PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
WHERE PartitionPeriod = N'M' and year(PeriodStartDate) >= 2014
UNION
SELECT  
	N'H' as PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
WHERE PartitionPeriod = N'Q' and year(PeriodStartDate) < 2014
GO
